
package com.bear.cmcc.NetTestHTTP.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.bear.cmcc.NetTestHTTP.component.HttpTestRoundInfoBean;
import com.bear.cmcc.NetTestHTTP.component.NetTestRoundInfoBean;
import com.bear.cmcc.NetTestHTTP.constant.HttpTestDBConstant;
import com.bear.cmcc.NetTestHTTP.constant.NetTestDBConstant;

/**
 * @author zhangheng
 * Http测试下载服务数据库操作类
 * 
 */
public class HttpTestDBControl extends SQLiteOpenHelper {
    String TAG = HttpTestDBControl.class.getSimpleName();

    /**
     * 创建数据库
     * 
     * @param context 父容器
     * @param _dbname 库名
     * @param _version 版本号
     */
    public HttpTestDBControl(Context context, String _dbname, int _version) {
        // 创建数据库
        super(context, _dbname, null, _version);
    }

    public HttpTestDBControl(Context context) {
        // 创建数据库
        super(context, HttpTestDBConstant.DBNAME, null, HttpTestDBConstant.DBVERSION);

    }

    /*
     * 创建测试round表
     */
    private void createTestRoundInfoTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME + " ("
                + HttpTestDBConstant.HttpTestRoundFiledName.pid + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.startTime + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.endTime + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.averageSpeed + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.up_averageSpeed + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.maxSpeed + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.minSpeed + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.testTimes + " TEXT,"                
                + HttpTestDBConstant.HttpTestRoundFiledName.isDelete + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.downServer + " TEXT,"
                + HttpTestDBConstant.HttpTestRoundFiledName.consumeTime + " TEXT" + ");");
    }

    /**
     * 重新创建表
     */
    private void createTestInfoTables(SQLiteDatabase db) {
        Log.i(TAG, "begin createTestInfoTables ");
            try {
                db.execSQL("DROP TABLE IF EXISTS " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME);
                createTestRoundInfoTable(db);
                Log.i(TAG, "createTestInfoTables ");
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
    
    public void reCreateTables()
    {
    	SQLiteDatabase db = null;
        try {
            db = this.getWritableDatabase();
	    	db.execSQL("DROP TABLE IF EXISTS " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME);
	        createTestRoundInfoTable(db);
        }
        catch (SQLException e) {
            Log.d(TAG, "reCreateTables Exception=>" + e.getMessage());
        } finally {
        	if(db!=null)
        		db.close();
        }
    }

    /**
     * 插入一条记录到round表
     * 
     * @param roundInfoBean
     * @return
     */
    public boolean insertToTestRoundInfo(HttpTestRoundInfoBean roundInfoBean) {
        SQLiteDatabase db = null;
        try {
            db = this.getWritableDatabase();
            String sql = "";
            if(roundInfoBean.getPID()==0)
            {
            	sql = "insert into " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME + " ("
                        + HttpTestDBConstant.HttpTestRoundFiledName.startTime + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.endTime + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.averageSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.maxSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.minSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.testTimes + ","                
                        + HttpTestDBConstant.HttpTestRoundFiledName.isDelete + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.downServer + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.consumeTime + ") values ('"
                        + roundInfoBean.getStartTime() + "','" + roundInfoBean.getEndTime() + "','"
                        + roundInfoBean.getAverageSpeed() + "','" + roundInfoBean.getMaxSpeed() + "','"
                        + roundInfoBean.getMinSpeed() + "','" + roundInfoBean.getTestTimes() + "','"
                        + "0" + "','" + roundInfoBean.getDownServer() + "','"
                        + roundInfoBean.getConsumeTime() + "')";
            }
            else
            {
            	sql = "insert into " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME + " ("
            			+ HttpTestDBConstant.HttpTestRoundFiledName.pid + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.startTime + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.endTime + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.averageSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.maxSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.minSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.testTimes + ","                
                        + HttpTestDBConstant.HttpTestRoundFiledName.isDelete + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.downServer + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.consumeTime + ") values ('"
                        + roundInfoBean.getPID() + "','"
                        + roundInfoBean.getStartTime() + "','" + roundInfoBean.getEndTime() + "','"
                        + roundInfoBean.getAverageSpeed() + "','" + roundInfoBean.getMaxSpeed() + "','"
                        + roundInfoBean.getMinSpeed() + "','" + roundInfoBean.getTestTimes() + "','"
                        + "0" + "','" + roundInfoBean.getDownServer() + "','"
                        + roundInfoBean.getConsumeTime() + "')";
            }
            db.execSQL(sql);
            return true;
        } catch (SQLException e) {
            Log.d(TAG, "insertToTestRoundInfo Exception=>" + e.getMessage());
            return false;
        } finally {
            db.close();
        }
    }
    
    
    /**
     * 更新一条记录到round表
     * 
     * @param roundInfoBean
     * @return
     */
    public boolean updateOrCreateTestRoundInfo(HttpTestRoundInfoBean roundInfoBean) {
        SQLiteDatabase db = null;
        try {
            db = this.getWritableDatabase();
            String sql = "";
            if(roundInfoBean.getPID()==0)
            {
                sql = "insert into " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME + " ("
                        + HttpTestDBConstant.HttpTestRoundFiledName.startTime + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.endTime + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.averageSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.maxSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.minSpeed + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.testTimes + ","                
                        + HttpTestDBConstant.HttpTestRoundFiledName.isDelete + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.downServer + ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.consumeTime + ") values ('"
                        + roundInfoBean.getStartTime() + "','" + roundInfoBean.getEndTime() + "','"
                        + roundInfoBean.getAverageSpeed() + "','" + roundInfoBean.getMaxSpeed() + "','"
                        + roundInfoBean.getMinSpeed() + "','" + roundInfoBean.getTestTimes() + "','"
                        + "0" + "','" + roundInfoBean.getDownServer() + "','"
                        + roundInfoBean.getConsumeTime() + "')";
            }
            else
            {
                sql = "update " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME + " set "
                        + HttpTestDBConstant.HttpTestRoundFiledName.startTime +" = '"+roundInfoBean.getStartTime()+"',"
                        + HttpTestDBConstant.HttpTestRoundFiledName.endTime +" = '"+roundInfoBean.getEndTime()+ "',"
                        + HttpTestDBConstant.HttpTestRoundFiledName.averageSpeed +" = '"+roundInfoBean.getAverageSpeed()+ "',"
                        + HttpTestDBConstant.HttpTestRoundFiledName.maxSpeed +" = '"+roundInfoBean.getMaxSpeed()+ "',"
                        + HttpTestDBConstant.HttpTestRoundFiledName.minSpeed +" = '"+roundInfoBean.getMinSpeed()+ "',"
                        + HttpTestDBConstant.HttpTestRoundFiledName.testTimes +" = '"+roundInfoBean.getTestTimes()+ "',"                                       
                        + HttpTestDBConstant.HttpTestRoundFiledName.isDelete +" = "+"'0'"+ ","
                        + HttpTestDBConstant.HttpTestRoundFiledName.downServer +" = '"+roundInfoBean.getDownServer()+ "',"
                        + HttpTestDBConstant.HttpTestRoundFiledName.consumeTime +" = '"+roundInfoBean.getConsumeTime()
                        +"'  where "+HttpTestDBConstant.HttpTestRoundFiledName.pid+" = '"+roundInfoBean.getPID()+"'";
                      
            }
            db.execSQL(sql);
            return true;
        } catch (SQLException e) {
            Log.d(TAG, "updateOrCreateTestRoundInfo Exception=>" + e.getMessage());
            return false;
        } finally {
            db.close();
        }
    }

 
    /**
     * 根据pid获取一轮round表的信息
     * 
     * @param _pid
     * @return
     */
    public HttpTestRoundInfoBean queryRoundInfoByPid(String _pid) {
        Cursor cursor = null;
        SQLiteDatabase db = null;
        try {
            db = getWritableDatabase();
            String sql = "select * from " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME
                    + " where pid =?";
            cursor = db.rawQuery(sql, new String[] {
                _pid
            });
            int count = cursor.getCount();
            if (count > 0) {
                cursor.moveToFirst();
                return dbSetRoundInfoBean(cursor);
            } else
                return null;
        } catch (Exception e) {
            Log.d(TAG, "queryRoundInfoByPid Exception=>" + e.getMessage());
            return null;
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            db.close();
        }
    }
    
    
    
    /**
     * 根据pid获取一轮round表的信息
     * 
     * @param _pid
     * @return
     */
    public HttpTestRoundInfoBean queryRoundInfoByStartTime(String startTime) {
        Cursor cursor = null;
        SQLiteDatabase db = null;
        try {
            db = getWritableDatabase();
            String sql = "select * from " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME
                    + " where "+HttpTestDBConstant.HttpTestRoundFiledName.startTime+" =?";
            cursor = db.rawQuery(sql, new String[] {
                    startTime
            });
            int count = cursor.getCount();
            if (count > 0) {
                cursor.moveToFirst();
                return dbSetRoundInfoBean(cursor);
            } else
                return null;
        } catch (Exception e) {
            Log.d(TAG, "queryRoundInfoByPid Exception=>" + e.getMessage());
            return null;
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            db.close();
        }
    }

    /**
     * 返回round表所有记录，即所有的轮测试的信息(降序排序)
     * 
     * @param needsDelete 如果false只返回未删除过的round信息，如果true返回全量
     * @return
     */
    public ArrayList<HttpTestRoundInfoBean> queryAllRoundInfo(boolean needsDelete) {
        Cursor cursor = null;
        SQLiteDatabase db = null;
        ArrayList<HttpTestRoundInfoBean> netTestRoundInfoBeans = new ArrayList<HttpTestRoundInfoBean>();
        try {
            db = getWritableDatabase();
            String sql = "select * from " + HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME
                    + " order by pid desc";
            cursor = db.rawQuery(sql, null);
            int count = cursor.getCount();
            if (count > 0) {
                if (needsDelete) {
                    while (cursor.moveToNext()) {
                    	HttpTestRoundInfoBean roundInfoBean = dbSetRoundInfoBean(cursor);
                        netTestRoundInfoBeans.add(roundInfoBean);
                    }
                } else {
                    while (cursor.moveToNext()) {
                        if (cursor
                                .getString(
                                        cursor.getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.isDelete))
                                .equals("0")) {
                        	HttpTestRoundInfoBean roundInfoBean = dbSetRoundInfoBean(cursor);
                            netTestRoundInfoBeans.add(roundInfoBean);
                        }
                    }
                }
                if (netTestRoundInfoBeans.size() > 0)
                    return netTestRoundInfoBeans;
                else
                    return null;
            } else
                return null;
        } catch (Exception e) {
            Log.d(TAG, "queryAllRoundInfo Exception=>" + e.getMessage());
            return null;
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            if (db != null) {
                try {
                    db.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 根据一次cursor返回roundInfoBean对象
     * 
     * @param cursor
     * @param roundInfoBean
     */
    private HttpTestRoundInfoBean dbSetRoundInfoBean(Cursor cursor) {
    	HttpTestRoundInfoBean roundInfoBean = new HttpTestRoundInfoBean();
        roundInfoBean.setPID(cursor.getInt(cursor
                .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.pid)));
        roundInfoBean.setStartTime(cursor.getString(cursor
                .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.startTime)));
        roundInfoBean.setEndTime(cursor.getString(cursor
                .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.endTime)));
        try {
            roundInfoBean.setAverageSpeed(Double.parseDouble(cursor.getString(cursor
                    .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.averageSpeed))));
        } catch (Exception e) {
            roundInfoBean.setAverageSpeed(0.0);
        }
        try {
            roundInfoBean.setMaxSpeed(Double.parseDouble(cursor.getString(cursor
                    .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.maxSpeed))));
        } catch (Exception e) {
            roundInfoBean.setMaxSpeed(0.0);
        }
        try {
            roundInfoBean.setMinSpeed(Double.parseDouble(cursor.getString(cursor
                    .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.minSpeed))));
        } catch (Exception e) {
            roundInfoBean.setMinSpeed(0.0);
        }
        try {
            roundInfoBean.setTestTimes(Integer.parseInt(cursor.getString(cursor
                    .getColumnIndex(NetTestDBConstant.TestRoundFiledName.testTimes))));
        } catch (Exception e) {
            roundInfoBean.setTestTimes(0);
        }
        roundInfoBean.setIsDelete(cursor.getString(cursor
                .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.isDelete)));
        roundInfoBean.setDownServer(cursor.getString(cursor
                .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.downServer)));
        roundInfoBean.setConsumeTime(cursor.getString(cursor
                .getColumnIndex(HttpTestDBConstant.HttpTestRoundFiledName.consumeTime)));
        return roundInfoBean;
    }


    /**
     * 传入_roundID,修改该round的信息isdelete为1，表示已删除
     * 
     * @param _roundID
     * @return
     */
    public boolean deleteRoundInfo(int _roundID) {
        SQLiteDatabase db = null;
        try {
            db = getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(HttpTestDBConstant.HttpTestRoundFiledName.isDelete, "1");
            db.update(HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME, values,
                    HttpTestDBConstant.HttpTestRoundFiledName.pid + "=?", new String[] {
                        String.valueOf(_roundID)
                    });
            return true;
        } catch (Exception e) {
            Log.d(TAG, "deleteRoundInfo Exception=>" + e.getMessage());
            return false;
        } finally {
            db.close();
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createTestInfoTables(db);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        createTestInfoTables(db);
        // createTestInfoTables(db);
        // db.execSQL("DROP TABLE IF EXISTS " +
        // HttpTestDBConstant.TABLES_TESTROUNDINFO_NAME);
        // db.execSQL("DROP TABLE IF EXISTS " +
        // HttpTestDBConstant.TABLES_TESTTIMEINFO_NAME);
        // db.execSQL("DROP TABLE IF EXISTS " +
        // HttpTestDBConstant.TABLES_TESTCELLINFO_NAME);
        // db.execSQL("DROP TABLE IF EXISTS " +
        // HttpTestDBConstant.TABLES_CONNECTINFO_NAME);
        // createTestRoundInfoTable(db);
        // createTestTimesInfoTable(db);
        // createTestCellInfoTable(db);
        // createTestConnectInfoTable(db);
    }

    /**
     * 查询表最后一条记录的pid值
     * 
     * @param tablename
     * @return 如果有pid值直接返回，没有返回0，表示表内目前没有记录
     */
    public int selectLastRowPid(String tablename) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = null;
        try {
            int pid;
            String sql = "select * from " + tablename + " order by pid desc LIMIT 1";
            cursor = db.rawQuery(sql, null);
            int count = cursor.getCount();
            if (count > 0) {
                cursor.moveToFirst();
                int pidindex = cursor.getColumnIndex("pid");
                // pid = cursor.getString(pidindex);
                pid = cursor.getInt(pidindex);
                return pid;
            } else {
                return 0;
            }
        } catch (Exception ex) {
            Log.d(TAG, "selectLastRowPid Exception=>" + ex.getMessage());
            return 0;
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            db.close();
        }
    }

    /**
     * 判断某张表是否存在
     * 
     * @param tabName 表名
     * @return boolean true存在，false不存在
     */
    public boolean tabbleIsExist(String tableName) {
        boolean result = false;
        if (tableName == null) {
            return false;
        }
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            if (db == null)
                db = getWritableDatabase();
            String sql = "select count(*) as c from Sqlite_master  where type ='table' and name ='"
                    + tableName.trim() + "' ";
            cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }
        } catch (Exception e) {
            return false;
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 查询表是否包含某个字段
     * 
     * @param tablename 表名，filedName 查询的字段
     * @return boolean true有这个字段，false没有这个字段
     */
    public boolean queryField(String tablename, String filedName) {
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = this.getWritableDatabase();
            String sql = "select * from sqlite_master where tbl_name ='" + tablename
                    + "' and sql like '%" + filedName + "%'";
            cursor = db.rawQuery(sql, new String[] {});
            int count = cursor.getCount();
            if (count > 0) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            Log.i("GYH", ex.getMessage());
            return false;
        } finally {
            try {
                if (cursor != null) {
                    cursor.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            db.close();
        }
    }

}
